<?php
require_once ( 'AbstractDAO.php' );
/**
 * Description of CommentDAO
 *
 * @author jszhang
 */
class ReviewDAO extends AbstractDAO 
{
	
	public function GetReviewAuthorId( $reviewId )
	{
		$sql = "select user_id from review where id = $reviewId";
		return $this->db->GetValue( $sql );
	}
	
	public function GetReviewInfo($rid) 
	{
		$sql="select id, user_id, shop_id, review_date, product_id_list, score,
			reality, service, velocity, rate, content, paymethod, feature, tag, view_count, flower_count, egg_count,
			response_count, pic_count from review where id=".$rid;
		return $this->db->GetRow($sql);
	}
	
	public function GetReviewByUid($uid, $perpage=6, $cpage=1, $sortby="review_date" )
	{
		$sql=" SELECT r.*,
						ubi.nick as nick,
						ubi.logo_path as logo_path,
						us.contribution as contribution,
						si.shop_name as shop_name
				 FROM 	review r,
					 	user_basic_info ubi,
					 	shop_info si,
					 	user_statistic us
			 	WHERE	r.user_id = ubi.id
			 	  AND	r.user_id = us.user_id
			 	  AND	r.shop_id = si.id
			 	  AND	r.user_id = $uid
				ORDER 	BY r.$sortby desc";
		
		/*
		select r.id as review_id, r.*, si.shop_name,
			(select nick from user_basic_info where id=r.user_id) as nick,
			(select logo_path from user_basic_info where id=r.user_id) as logo_path,
			(select contribution from user_statistic where user_id=r.user_id) as contribution				
			from review r, shop_info si where r.shop_id=si.id and
            r.user_id=".$uid." order by r.$sortby desc";
        */
		return $this->getRows( $sql,$perpage,$cpage );
	}

	public function UpdateReviewViewCount($rid)
	{
		$sql = "update review set view_count = view_count +1 where id=".$rid;
		$this->db->Update($sql);
	}
	
	public function GetPictureByRid($rid) {
		$sql="select picture_path,name from review_picture where review_id=".$rid;
		return $this->db->GetPlan($sql);
	}

	public function GetFlowerByRid($rid) {
		$sql="select user_basic_info.id, user_basic_info.nick, user_basic_info.logo_path from user_basic_info,
			review_flower where review_flower.review_id=".$rid." and review_flower.user_id=user_basic_info.id";
		return $this->db->GetPlan($sql);
	}

	public function GetResponseByRid($rid) 
	{			
		$num = $this->db->GetValue("select response_count from review where id=".$rid);			 
		
		$sql = "select * from review_response where review_id =$rid order by date desc";
	
        $result=array();
		$result['total_num']=$num;
	    if($num!='0'){
	    	
	    	$result['result']=$this->db->GetPlan($sql);
	    }
		
		return $result;	
			
//			
//		$result=array();
//		$sql="select response_count from review where id=".$rid;
//		$count=$this->db->GetValue($sql);
//		$result['total_num']=$count;
//		if($count=='0')
//		{
//			$result['res']=false;
//		}
//		else
//		{
//			$sql="select user_id, user_ip, is_anonymous, date, content from review_response where
//			review_id=".$rid;
//			$result['res']=$this->getRows($sql, $perpage, $cpage);
//		}
//		return $result;
	}
	
	/**
	 *
	 * @param int $id
	 * @param int $perpage
	 * @param int $currentPage
	 * @return array
	 */
	public function GetByShopId( $sid, $perpage, $cpage=1, $sortby="review_date", $order="desc" ) {
		if( intval($sid) == 0 ||
			intval($perpage) == 0 ||
			intval($cpage) == 0) {
                        return false;
		}

		$sql = "SELECT	r.*,
						ubi.nick as nick,
						ubi.logo_path as logo_path,
						us.contribution as contribution
				 FROM 	review r,
					 	user_basic_info ubi,
					 	user_statistic us
			 	WHERE	r.user_id = ubi.id
			 	  AND	r.user_id = us.user_id
			 	  AND	r.shop_id = $sid
				ORDER 	BY	$sortby $order";
		
		
//		$sql = "select *,
//				(select nick from user_basic_info where id=r.user_id) as nick,
//				(select logo_path from user_basic_info where id=r.user_id) as logo_path,
//				(select contribution from user_statistic us where us.user_id=r.user_id) as contribution from review r where shop_id=".$sid.
//				" order by $sortby $order";
                
		$rows = $this->getRows( $sql,$perpage,$cpage );

		return $this->GetReviewDetail( $rows );
	}
    
    /**
	 *
	 * @param int $id
	 * @param int $perpage
	 * @param int $currentPage
	 * @return array
	 */
	public function GetByProductId( $pid, $perpage, $cpage=1, $sortby="review_date", $order="desc" ) {
		if( intval($pid) == 0 ||
			intval($perpage) == 0 ||
			intval($cpage) == 0) {
                        return false;
		}

		$sql = "SELECT	r.*,
						ubi.nick as nick,
						ubi.logo_path as logo_path,
						us.contribution as contribution
				 FROM 	review r,
					 	user_basic_info ubi,
					 	user_statistic us
			 	WHERE	r.user_id = ubi.id
			 	  AND	r.user_id = us.user_id
			 	  AND	r.product_id_list = '$pid'
				ORDER 	BY	$sortby $order";
		
		
//		$sql = "select *,
//				(select nick from user_basic_info where id=r.user_id) as nick,
//				(select logo_path from user_basic_info where id=r.user_id) as logo_path,
//				(select contribution from user_statistic us where us.user_id=r.user_id) as contribution from review r where shop_id=".$sid.
//				" order by $sortby $order";
                
		$rows = $this->getRows( $sql,$perpage,$cpage );

		return $this->GetReviewDetail( $rows );
	}

	public function GetByPlatId( $platid, $perpage, $cpage=1, $sortby="review_date", $order="desc" ) {
		if( 
			intval($perpage) == 0 ||
			intval($cpage) == 0) {
                        return false;
		}

		if($platid=="0")
		  $platsql="";
		else  
		  $platsql="AND	r.plat_id = $platid";
		  		
		$sql = "SELECT	r.*,
						si.shop_name, 
						ubi.nick as nick,
						ubi.logo_path as logo_path,
						us.contribution as contribution
				 FROM 	review r,
					 	user_basic_info ubi,
					 	user_statistic us,
					 	shop_info si
			 	WHERE	r.user_id = ubi.id 
			 	  AND   r.shop_id = si.id 
			 	  AND	r.user_id = us.user_id ".$platsql." ORDER BY $sortby $order";
		
		
//		$sql = "select *,
//				(select nick from user_basic_info where id=r.user_id) as nick,
//				(select logo_path from user_basic_info where id=r.user_id) as logo_path,
//				(select contribution from user_statistic us where us.user_id=r.user_id) as contribution from review r where shop_id=".$sid.
//				" order by $sortby $order";
                
		$rows = $this->getRows( $sql,$perpage,$cpage );

		return $this->GetReviewDetail( $rows );
	}	
	
	/**
	 * get the total review count by shop id
	 * @param int $sid
	 * @return int
	 */
	public function GetReviewCountByShopId( $sid ){
		$sid = intval( $sid );
		$sql = "select review_count from shop_info where id=$sid";

		$count = $this->db->GetValue( $sql );
		if( $count )
		{
			return $count;
		}
		else{
			return 0;
		}
	}

	/**
	 * get the total review count by user id
	 * @param int $sid
	 * @return int
	 */
	public function GetReviewCountByUserId( $uid ){
		$uid = intval( $uid );
		$sql = "select review_count from user_statistic where user_id=$uid";

		$count = $this->db->GetValue( $sql );
		if( $count ){
			return $count;
		}
		else{
			return 0;
		}
	}
	
	public function GetReviewCountByPlatId( $platid )
	{
		if($platid=="0"){
			$sql = "select SUM(review_count) from shop_info";
		}else{
			$platid = intval( $platid );
			$sql = "select SUM(review_count) from shop_info where plat_id=$platid";
		}

		$count = $this->db->GetValue( $sql );
		if( $count )
		{
			return $count;
		}
		else{
			return 0;
		}
	}

	/**
	 *
	 * @param int $id
	 * @param int $perpage
	 * @param int $currentPage
	 * @return array
	 */
	public function GetByIdList( $ids, $isDetail=true ) {
		if( count( $ids ) == 0 ){
			return false;
		}
		
		$strIds = implode( ",", $ids );

		//too many quries in this function, TODO
		$sql = "SELECT	r.*,
						ubi.nick as nick,
						ubi.logo_path as logo_path,
						us.contribution as contribution,
						si.logo_url as logo_url,
						si.shop_name as shop_name
				FROM 	review r,
					 	user_basic_info ubi,
					 	shop_info si,
					 	user_statistic us
			 	WHERE	r.user_id = ubi.id
			 	  AND	r.user_id = us.user_id
			 	  AND	r.shop_id = si.id
			 	  AND 	r.id in (".$strIds.")
			 	ORDER BY FIELD(r.id, ".$strIds.")";
                
//                $sql = "select *,
//				(select nick from user_basic_info where id=r.user_id) as nick,
//				(select logo_path from user_basic_info where id=r.user_id) as logo_path,
//				(select contribution from user_statistic us where us.user_id=r.user_id) as contribution,
//				(select logo_url from shop_info where id=r.shop_id) as logo_url,
//				(select shop_name from shop_info where id=r.shop_id) as shop_name
//				from review r where r.id in (".$strIds.") ORDER BY FIELD(r.id, ".$strIds.")";

		$rows = $this->db->GetPlan( $sql );
		if($isDetail==true)
			return $this->GetReviewDetail( $rows );
		else
			return $rows;
		
	}

	public function InsertResponse($rid, $review_uid, $user_id, $user_ip, $is_anonymous, $content)
	{
		$now=GetCurrentDate();
		$userDAO=GetUserDAO();
	    if($user_id>'0'&&$userDAO->InBlackList($review_uid, $user_id)===true)
	    	return '0';
		try
	    {
	    	$this->db->BeginTransaction();
			$sql="Insert into review_response(review_id, user_id, user_ip, is_anonymous, date, content) values (".$rid.", ".
			$user_id.", '".$user_ip."', ".$is_anonymous.", '".$now."', '".$content."')";
			$this->db->Insert($sql);
			$sql="Update review set response_count=response_count+1 where id=".$rid;
			$this->db->Update($sql);
			
			 $this->db->Commit();
	    	return '1';
		}
	    catch( Exception $e)
	    {
	    	$this->db->Rollback();
	    	return '-1';
	    }
	   
	}

	////////////////////////////////////////////////////////////////////////////////////
	// protected functions goes here
	/**
	 * get review details
	 * @param array $reviews
	 * @return array
	 */
	public function GetReviewDetail( $reviews ){
		$result = array();
		
		if( !$reviews )
			return $result;
		
		foreach ($reviews as $item) {
			if( !edp_is_empty_string( $item['tag'] ) ){
				$item['tags'] = array();
				$tags = explode( " ", $item['tag'] );
				foreach( $tags as $tag ){
					if( !is_null($tag) &&  !empty( $tag )  )
						$item['tags'][] = $tag;
				}
			}
			//set the default value to false
			else
				$item['tags'] = false;

			$item['review_date'] = $item['review_date'];
			
			if(!edp_is_empty_string($item['product_id_list'])){//add by bo,2010/4/5
				$sql = "select * from product_info where status=1 and id in (".$item['product_id_list'].")";
				$item['product_list'] = $this->db->GetPlan( $sql );
			}
			else
				$item['product_list'] = false;

			$sql = "select * from review_picture where review_id=".$item['id'];
			$item['pic_list'] = $this->db->GetPlan( $sql );

			$result[] = $item;
		
		}

		return $result;
	}
	
	//Used by page platform
	function GetRecentReviewByPlatid( $platId, $count = 5)
	{
		$cacheManager = GetCacheManager();
		$re = $cacheManager->Get( "recentReviewByPlatid_".$platId."_".$count, CNS_REVIEW );
		if( $re !== false )
			return $re;
				
		$searchEngine = GetReviewSearch();
		$ids = $searchEngine->GetRecentReviewByPlatid( $count, $platId );
		
		if( $ids === false )
			return false;
		

		$sql = "select 	shop_info.id as sid, 
						shop_info.shop_name, 
						shop_info.logo_url, 
						user_basic_info.nick,
						user_basic_info.logo_path,
						review.id as rid,
						review.user_id as uid, 
						review.review_date, 
						review.score, 
						review.reality, 
						review.service, 
						review.velocity, 
						review.rate, 
						review.content 
				from 	review, 
						shop_info,
						user_basic_info
			    where 	shop_info.id = review.shop_id 
			      and   review.user_id = user_basic_info.id
			      and 	review.id in( " . implode(',', $ids) . ")";
		
		return $this->db->GetPlan( $sql, true, CNS_REVIEW, "recentReviewByPlatid_".$platId."_".$count );
		
	}
	
	function GetRecentReviewByUid($uid, $count=5, $sortby="review_date" )
	{
		$sql = "select 	r.id as review_id, 
					   	r.*, 
					   	si.shop_name 
				  from 	review r, 
				  		shop_info si
				 where 	r.shop_id = si.id 
				   and  r.user_id = " . $uid . " 
			  order by 	r.$sortby desc limit 0, " . $count;

		$rows = $this->db->GetPlan($sql);	
		return 	 $this->GetReviewDetail( $rows );	
	}
	
	
	function AddReview($user_id, $shop_id, $review_date, $product_id_list,
		$score, $reality, $service, $velocity, $rate, $content, $paymethod,
		$feature, $tag, $view_count, $flower_count, $egg_count, $response_count,
		$pic_count, $product_count, $uploaded_files, $product_id, $taobao_review_id )
	{
		$shopDAO=GetShopDAO();
		$productDAO=GetProductDAO();
		$eventDispatcher = GetEventDispatcher();
		
		try
		{
			$this->db->BeginTransaction();	

            
//			if($shopDAO->UpdateFirstReviewer($shop_id, $user_id, $review_date)===true)
//			{
//				//FIRST_REVIEW_SHOP
//				$para['sid']=$shop_id;
//				if($product_count>'0')
//					$para['pid']=$product_id_list;
//				$eventDispatcher->Notify( $user_id, FIRST_REVIEW_SHOP, $para);
//			}
//			else
//			{
//				$productDAO=GetProductDAO();
//				if($product_count>'0'&&$productDAO->UpdateFirstReviewer($product_id_list, $user_id, $review_date)===true)
//				{
//					//FIRST_REVIEW_PRODUCT
//					$para['sid']=$shop_id;
//					$para['pid']=$product_id_list;
//					$eventDispatcher->Notify( $user_id, FIRST_REVIEW_PRODUCT, $para);
//				}
//				else
//				{
//					if($pic_count>'0')
//					{
//						//REVIEW_INCLUDE_PIC
//						$para['sid']=$shop_id;
//						if($product_count>'0')
//							$para['pid']=$product_id_list;
//						$eventDispatcher->Notify( $user_id, REVIEW_INCLUDE_PIC, $para);
//					}
//					else
//					{
//						if($product_count>'0')
//						{
//							//REVIEW_INCLUDE_PRODUCT
//							$para['sid']=$shop_id;
//							$para['pid']=$product_id_list;
//							$eventDispatcher->Notify( $user_id, REVIEW_INCLUDE_PRODUCT, $para);
//						}
//						else
//						{
//							//NORMAL_REVIEW
//							$para['sid']=$shop_id;
//							$eventDispatcher->Notify( $user_id, NORMAL_REVIEW, $para);
//						}
//					}
//				}
//			}
			
			$sql = "select 	plat_id 
					  from 	shop_info
					 where  id = $shop_id ";
			$plat_id = $this->db->GetValue( $sql );
			//Add review Content
			$reid = $this->AddReviewContent( $user_id, $shop_id, $plat_id, $review_date, $product_id_list,
			$score, $reality, $service, $velocity, $rate, $content, $paymethod,
			$feature, $tag, $view_count, $flower_count, $egg_count, $response_count,
			$pic_count, $product_count, $product_id, $taobao_review_id );

			//Upload pictures
			$pic_id_list = '';
			foreach( $uploaded_files as $key => $value ) 
			{
				$p_id = $this->AddPicInfo( $reid, $uploaded_files[$key]['path'],
					$uploaded_files[$key]['name'],$user_id,$review_date );
				
				$pic_id_list .= $p_id . ',';
			}
			
			$pic_id_list = trim( $pic_id_list, ',' );
			
			//update the pic_id_list in the review
			
			$sql = " update review
					 set pic_id_list = '$pic_id_list'
					 where id = $reid";
			$this->db->Update( $sql );
			
			$this->db->Commit();
	    	return true;
		}
		catch( Exception $e)
	    {
	    	$this->db->Rollback();
	    	return false;
	    }
	    
	}
	
	/**********************************************************
	 *	Add new review
	 *	Seal Created by 2009-11-15
	 **********************************************************/
	function AddReviewContent( $user_id, $shop_id, $plat_id, $review_date, $product_id_list,
		$score, $reality, $service, $velocity, $rate, $content, $paymethod,
		$feature, $tag, $view_count, $flower_count, $egg_count, $response_count,
		$pic_count, $product_count, $product_id, $taobao_review_id )
	{
		//Add review Content
		$sql = "insert into 
				review( user_id, shop_id, plat_id, review_date, product_id_list, score, reality,
						service, velocity, rate, content, paymethod, feature, tag, view_count,
						flower_count, egg_count, response_count, pic_count, product_count, product_id, pic_id_list, taobao_review_id )
				values( $user_id, $shop_id, $plat_id, '$review_date', '$product_id_list', $score, $reality,
						$service, $velocity, $rate, '$content', '$paymethod', '$feature', '$tag', $view_count,
						$flower_count, $egg_count, $response_count, $pic_count, $product_count, $product_id, '', '$taobao_review_id' )";
		$this->db->Insert( $sql );
		$rid=$this->db->LastID();
		
		//Add User review Count
		$sql = " update user_statistic set review_count = review_count + 1 where user_id = $user_id ";
		$this->db->Update( $sql );
			
		//Add User pic count
		if( $pic_count > 0 )
		{
			$sql = " update user_statistic set pic_count = pic_count + $pic_count where user_id = $user_id ";
			$this->db->Update( $sql );
		}
			
		//Add Shop review Count
		$sql="update shop_info set review_count=review_count+1 where id=$shop_id";
		$this->db->Update( $sql );
			
		
		/**
		 * modify by bo 2010-07-26
		 */
		//Add Product review Count
//		if( $product_id > 0 )
//		{
//			$sql="update product_info set review_count=review_count+1 where id = $product_id";
//			$this->db->Update( $sql );
//		}
/*
		if(!edp_is_empty_string($product_id_list)){
			
			$pid_arr = explode(',',$product_id_list);
			foreach($pid_arr as $k=>$v){
				$sql="update product_info set review_count=review_count+1 where id = $v";
     			$this->db->Update( $sql );			
			}
		}
*/
		/**
		 * modify by yawei 2010-07-27
		 */		
		if(!edp_is_empty_string($product_id_list)){
				$sql="update product_info set review_count=review_count+1 where id in($product_id_list)";
     			$this->db->Update( $sql );			
		}
		
		return $rid;
	}
	
	
	function UpdateReview( $params, $del_pics, $remain_pics )
	{
		//$Logger = GetLogger();
		//$Logger->error( "del_pics: $del_pics, remain_pics: $remain_pics, delta:". $params['pic_count'] );
		try
		{
			$this->db->BeginTransaction();	
			
			/*
			 * delete pictures removed by uer, from database and file system
			 */
			
			if( trim($del_pics) != '')
			{
				$pics = $this->db->GetPlan( "select picture_path from review_picture where id in( $del_pics)" );
				if( $pics != false )
				{
					foreach( $pics as $key => $value )
					{
						//echo "delete: " . ROOT_DIR . $value['picture_path'];
						unlink( EdpGetPicPath( $value['picture_path'] ) );
						unlink( EdpGetSmallPicPath( $value['picture_path']) );
					}
					
					$this->db->Update( "delete from review_picture where id in( $del_pics)" );
				}
			}

			/*
			 * Add new pictures into the database
			 */
			$new_pic_ids = array();			
			foreach( $params['uploaded_files'] as $key => $value ) 
			{
				$new_pic_ids[] = $this->AddPicInfo( $params['id'], $params['uploaded_files'][$key]['path'],
					$params['uploaded_files'][$key]['name'], $params['user_id'], $params['review_date'] );
			}
			
			if( count($new_pic_ids) > 0 )
				$new_pic_ids =  implode( ",", $new_pic_ids ) . ( $remain_pics == "" ? "" : ',' . $remain_pics );
			else
				$new_pic_ids = $remain_pics;
				
			//echo "new pic ids:" . $new_pic_ids;
			
			/*
			 * Update the review information
			 */
			$sql = "update review set
						user_id = ".$params['user_id'].",
						shop_id = ".$params['shop_id'].",
						review_date = '".$params['review_date']."',
						product_id_list = '". $params['product_id_list']."',
						score = ".$params['score'].",
						reality = ".$params['reality'].",
						service = ".$params['service'].",
						velocity = ".$params['velocity'].",
						rate = ".$params['rate'].",
						content = '".$params['content']."',
						paymethod = '".$params['paymethod']."',
						feature = '".$params['feature']."',
						tag = '".$params['tag']."',
						view_count = ".$params['view_count'].",
						flower_count = ".$params['flower_count'].",
						egg_count = ".$params['egg_count'].",
						response_count = ".$params['response_count'].",
						pic_count = pic_count + (".$params['pic_count']."),
						product_count = ".$params['product_count'].",
						pic_id_list = '" . $new_pic_ids . "'
						where id=".$params['id'];
			$this->db->Update( $sql );
			
			$sql = "update user_statistic set pic_count = pic_count + ( " . $params['pic_count'] . ')
					where user_id = ' . GetLoginUserID();
			$this->db->Update( $sql );
			
			$this->db->Commit();
	   
	   		return true;
		}
		catch( Exception $e)
	    {
	    	$this->db->Rollback();
	    	return false;
	    }
	   
	}
	
	
	/**********************************************************
	 *	Record picture info in a reveiw
	 *	Seal Created by 2009-11-15
	 **********************************************************/
	function AddPicInfo( $review_id, $picture_path, $name, $userid, $pubdate )
	{
		$sql = "insert into
				review_picture( review_id, picture_path, name, user_id, pub_date)
				values( $review_id, '$picture_path', '$name', $userid, '$pubdate' )";
		$this->db->Insert( $sql );
		return $this->db->LastID();
	}
	
	/**********************************************************
	 *	Simply get review infomation by ID
	 *	Seal Created by 2009-12-27
	 **********************************************************/
	function GetReviewById( $review_id )
	{
		$sql = "select  r.id,
						r.content,
						(select nick from user_basic_info where id = r.user_id) username,
						(select shop_name from shop_info where id = r.shop_id) shopname, 
						r.review_date	   
				from review r
				where r.id = $review_id ";
		
		return $this->db->GetPlan( $sql );
	}

	/**
	 *
	 * @param int $review_id
	 * @return one row
	 */
	public function GetReviewDetailById( $review_id )
	{
		$sql = "select  r.*,
						(select nick from user_basic_info where id = r.user_id) username,
						(select shop_name from shop_info where id = r.shop_id) shopname,
						r.review_date
				from review r
				where r.id = $review_id ";

		return $this->db->GetRow( $sql );
	}
	
    function CheckReviewFlowerStatus($uid, $rid){
	    	
	    $sql= "select * from review_flower where user_id = $uid and review_id = $rid";

		$result = $this->db->GetRow( $sql );
		
		if($result){
			//in database 
			return true;
			
		}else{
            //not in database
			return false;
		}
	    	
    }
    
    
	function CheckReviewEggStatus($uid, $rid){
	    	
	    $sql= "select * from review_egg where user_id = $uid and review_id = $rid";

		$result = $this->db->GetRow( $sql );
		
		if($result){
			//in database 
			return true;
			
		}else{
            //not in database
			return false;
		}
	    	
    }
    
    function AddReviewFlower($uid, $rid)
    {
    	$sql="select user_id from review where id=$rid";
	    if($this->db->GetValue($sql)==$uid)
	    	return '0';
	    {
	    	$now=GetCurrentDate();
	    	try
		    {
		    	$this->db->BeginTransaction();
		    	$sql = "insert into 
						review_flower(user_id, review_id,date) values( $uid, $rid,'$now')";
				$this->db->Insert( $sql );	
				$sql = "update review set flower_count=flower_count+1 where id= $rid";
				$this->db->Update( $sql );	
				$sql = "update user_statistic set flower_count=flower_count+1 where user_id=(select user_id from 
				review where id=$rid)";
				$this->db->Update( $sql );	
				
				$this->db->Commit();
		   	 return '1';
			}
		    catch( Exception $e)
		    {
		    	$this->db->Rollback();
		    	return '-1';
		    }	
	    }
    }
    		    
    function AddReviewEgg($uid, $rid)
    {
	    $review_uid=$this->db->GetValue("select user_id from review where id=$rid");
	    if(!$review_uid)
	    	return '-2';
	    else
	    {
	    	if($review_uid==$uid)
	    		return '0';
	    	else
	   	 	{
		    	$userDAO=GetUserDAO();	
		    	if($userDAO->InBlackList($review_uid, $uid))
		    		return '-1';	
		    	else
		    	{
		    		try
		    		{
		    			$now=GetCurrentDate();
				    	$this->db->BeginTransaction();
				    	$sql = "insert into 
								review_egg(user_id, review_id,date) values( $uid, $rid,'$now')";
						$this->db->Insert( $sql );	
						$sql = "update review set egg_count=egg_count+1 where id= $rid";
						$this->db->Update( $sql );	
						$sql = "update user_statistic set egg_count=egg_count+1 where user_id=(select user_id from 
							review where id=$rid)";
						$this->db->Update( $sql );
						
						$this->db->Commit();
				    	return '1';	
					}
				    catch( Exception $e)
				    {
				    	$this->db->Rollback();
				    	return '-2';
				    }	
	    		}
    		}
	    }
    }
    
    
	function DeleteReviewFlower($uid, $rid)
	{
		try
	    {
	    	$this->db->BeginTransaction();	
	        $sql = "delete from 
	                review_flower where user_id = $uid and review_id = $rid";	
			$this->db->Update( $sql );	
			$sql = "update review set flower_count=flower_count-1 where id= $rid";
			$this->db->Update( $sql );	
			$sql = "update user_statistic set flower_count=flower_count-1 where user_id=(select user_id from 
			review where id=$rid)";
			$this->db->Update( $sql );	
			
			$this->db->Commit();
	    	return '1';	
		}
	    catch( Exception $e)
	    {
	    	$this->db->Rollback();
	    	return '-1';
	    }	
    }
    
	function DeleteReviewEgg($uid, $rid)
	{
		try
	    {
	    	$this->db->BeginTransaction();
	        $sql = "delete from 
	                review_egg where user_id = $uid and review_id = $rid";		
			$this->db->Update( $sql );	
			$sql = "update review set egg_count=egg_count-1 where id= $rid";
			$this->db->Update( $sql );
			$sql = "update user_statistic set egg_count=egg_count-1 where user_id=(select user_id from 
				review where id=$rid)";
			$this->db->Update( $sql );	
			
			 $this->db->Commit();
	    	return '1';	
		}
	    catch( Exception $e)
	    {
	    	$this->db->Rollback();
	    	return '-1';
	    }	
    }
    
    /*
    function IncreasePicCount( $uid )
    {
    	$sql = "update user_contribution
    			set pic_count = pic_count + $count";
    	$this->db->Update( $sql );
    }
    */
	    
	/**
	 * delete a review and delete its pics
	 */
	public function DeleteReview( $userid, $rid )
	{
		
		$sql = "select id, review_id, picture_path
				 from review_picture
				 where review_id = $rid";

		
		$userDAO = GetUserDAO();
		$pics = $this->db->GetPlan( $sql );
		try{
			
			$this->db->BeginTransaction();
			
			$sql = "select shop_id, 
						   product_id,
			 			   product_id_list, 
			 			   pic_count 
					 from  review
					where  id = $rid
					  and  user_id = $userid
					  for update";
			
			$review_info = $this->db->GetRow( $sql );
			
			if( $review_info === false )
			{
				$this->db->Commit();
				return false;
			}
			
			if( $pics )	{
				foreach ( $pics as $key => $pic ) {
					@unlink( EdpGetPicPath( $pic['picture_path'] ) );
					@unlink( EdpGetSmallPicPath($pic['picture_path']) );
				}
			}
			
			
			$sql = "delete from review where id = $rid and user_id = $userid";
			$this->db->Update( $sql );
			
			$sql = "delete from review_picture where review_id = $rid ";
			$this->db->Update( $sql );
			
			$sql = "delete from review_egg where review_id = $rid ";
			$this->db->Update( $sql );
			
			$sql = "delete from review_flower where review_id = $rid ";
			$this->db->Update( $sql );
			
			$sql = "delete from review_response where review_id = $rid ";
			$this->db->Update( $sql );
			
			$coins = 3;
			if( $review_info['pic_count'] > 0 )
				$coins += 3;
			
			if( trim($review_info['product_id_list']) != '' )
				$coins += 3;
				
			$picNum = $pics === false ? 0 : count( $pics );
                        
			$sql = "update user_statistic
					set review_count = review_count - 1,
						pic_count = (pic_count - $picNum),
						contribution = case when  contribution > $coins then (contribution - $coins) else 0 end,
		    			coins = case when  coins > $coins then (coins - $coins) else 0 end
					where user_id = $userid";
			
			$this->db->Update( $sql );
			
			$sql = "update shop_info
					   set review_count = review_count - 1
					 where id = " . $review_info['shop_id'];
			$this->db->Update( $sql );
			
			if( $review_info['product_id'] != '' ) {
				$sql = 'update product_info
						   set review_count = review_count - 1
						 where id = ' . $review_info['product_id'];
				$this->db->Update( $sql );
			}
			
			$this->db->Commit();
			return true;
			
		}catch( Exception $e ){
			
			$this->db->Rollback();
	    	return false;
		}
	}

    /**
     * return review category list by user id
     *
     * @param int $userid
     * @return array
     */
    public function GetReviewCategoryByUserId( $userid )
    {
    	$cacheManager = GetCacheManager();
		$re = $cacheManager->Get( "reviewCategoryByUid_".$userid, CNS_REVIEW );
		if( $re !== false )
			return $re;
			
        //get id list from database first
        $sql = "select s.cate_id_list from review r inner join shop_info s on s.id=r.shop_id where r.user_id=$userid";
        $cate_ids = $this->db->GetPlan( $sql );

		//make it a map
		$result = array();
		if( $cate_ids ){
			foreach( $cate_ids as $cateid ){
				$idarr = explode( ",", $cateid['cate_id_list'] );
				foreach( $idarr as $id ){
					$item = array( 'name'=>'', 'count'=> isset($result["$id"])? $result["$id"]['count']+1 : 1 );
					$result["$id"] = $item;
				}
			}

			//finally get all category name
			$cateid_list = array_keys($result);
			$strCateIds = implode(",", $cateid_list);
			$sql = "select id, cate_name from shop_category where id in ($strCateIds)";
			$cateMaps = $this->db->GetPlan( $sql );
			foreach( $cateMaps as $item ){
				$key = $item['id'];
				$result["$key"]['name'] = $item['cate_name'];
			}
		}
		$cacheManager->Set( "reviewCategoryByUid_".$userid, $result, CNS_REVIEW );
        return $result;
    }

    /**
     * return all user tag and review count by tag
     *
     * @param int $userid
     * @return array
     */
    public function GetReviewTagByUserId( $userid )
    {
    	$cacheManager = GetCacheManager();
		$re = $cacheManager->Get( "reviewTagByUid_".$userid, CNS_REVIEW );
		if( $re !== false )
			return $re;
			
        $sql = "select tag from review where user_id=$userid";
        $tags = $this->db->GetPlan( $sql );

        $result = array();
		if( $tags ){
			foreach( $tags as $taglist ){
				//echo $taglist['tag'];
				if( !edp_is_empty_string($taglist['tag']) ){
					$tagarr = explode( " ", $taglist['tag'] );
					foreach( $tagarr as $tag ){
						if( isset($result["$tag"]) ){
							$result["$tag"] += 1;
						}
						else{
							$result["$tag"] = 1;
						}
					}
				}
			}
		}
		$cacheManager->Set( "reviewTagByUid_".$userid, $result, CNS_REVIEW );
        return $result;
    }
    
    public function GetReviewPics( $rid )
    {
    	$sql = "select id, picture_path, name
    			from review_picture
    			where review_id = :review_id";
    	$psql = $this->db->CreatePreparedStatement( $sql );
    	$psql->AddParameter( "review_id", SQLTypes::INTEGER, $rid );
    	return $this->db->GetPlan( $psql );
    }
    
    public function GetReviewProducts( $ids )
    {
    	if( trim($ids) == '' )
    		return false;
    	
    	$sql = "select id, product_name
    			from product_info
    			where id in (:ids)";
    	$psql = $this->db->CreatePreparedStatement( $sql );
    	$psql->AddParameter( "ids", SQLTypes::NATIVE, $ids );
    	return $this->db->GetPlan( $psql );
    }
    
	public function GetReviewProdPic( $rid )
    {
    	$sql = "select product_id_list, pic_id_list, product_id
    			from review
    			where id = :rid";
    	$psql = $this->db->CreatePreparedStatement( $sql );
    	$psql->AddParameter( "rid", SQLTypes::INTEGER, $rid );
    	return $this->db->GetRow( $psql );
    }
    
    public function GetPictureShopByUid($uid)
    {
    	$sql="select si.id, si.shop_name from review_picture rp, review r, shop_info si where rp.user_id=:userID and rp.review_id=r.id and r.shop_id=
    	si.id group by si.id order by count(*) desc";
    	
    	$psql = $this->db->CreatePreparedStatement( $sql );
    	$psql->AddParameter( "userID", SQLTypes::INTEGER, $uid );
    	return $this->db->GetPlan( $psql );
    }
    
    public function GetPictureCountBySid($sid, $uid)
    {
    	$sql="select count(*) as pic_count from review_picture rp, review r where r.shop_id=:shopID 
    	and r.user_id=:userID and r.id=rp.review_id";
    	$psql = $this->db->CreatePreparedStatement( $sql );
    	$psql->AddParameter( "shopID", SQLTypes::INTEGER, $sid );
    	$psql->AddParameter( "userID", SQLTypes::INTEGER, $uid );
    	return $this->db->GetValue( $psql );
    }
    
    public function GetHotReview( $count = 5 )
    {
    	$cacheManager = GetCacheManager();
		$re = $cacheManager->Get( 'hotReview_'.$count, CNS_REVIEW );
		
		if( $re!= false )
			return $re;
		
		$searchEngine = GetReviewSearch();
		$ids = $searchEngine->GetHotReview( $count );
		
		if( $ids === false )
			return false;
		
		$sql = "select 	r.id, 
				    	r.user_id, 
				    	ubi.nick, 
				    	ubi.logo_path,
				    	r.shop_id, 
				    	si.shop_name, 
				    	si.logo_url, 
				    	r.score, 
				    	r.reality, 
				    	r.service, 
				    	r.velocity, 
				    	r.rate, 
				    	r.content,
				    	r.response_count, 
				    	r.egg_count 
    	from review r, 
    		 shop_info si, 
    		 user_basic_info ubi 
    	where r.user_id=ubi.id 
    	  and r.shop_id=si.id 
     	  and r.id in( " . implode(',', $ids) . ")";
				
		return $this->db->GetPlan($sql, true, CNS_REVIEW, 'hotReview_'.$count);
    }
    
    public function GetNewReview( $count = 5 )
    {
    	$cacheManager = GetCacheManager();
		$re = $cacheManager->Get( 'newReview_'.$count, CNS_REVIEW );
		
		if( $re!= false )
			return $re;
			
		$sql = "select 	r.id, 
				    	r.user_id, 
				    	ubi.nick, 
				    	ubi.logo_path,
				    	r.shop_id, 
				    	si.shop_name, 
				    	si.logo_url, 
				    	r.score, 
				    	r.reality, 
				    	r.service, 
				    	r.velocity, 
				    	r.rate, 
				    	r.content,
				    	r.response_count, 
				    	r.egg_count 
    	from review r, 
    		 shop_info si, 
    		 user_basic_info ubi 
    	where r.user_id=ubi.id 
    	  and r.shop_id=si.id 
    	  and CHAR_LENGTH( r.content ) >100
     	  order by r.review_date desc limit 0, " . $count;
		
		return $this->db->GetPlan($sql);
    }    
    
    public function ReviewNeedWait( $uid, $shopID )
    {
    	$sql = "select TIMESTAMPDIFF( SECOND, review_date, NOW()) from review where user_id=$uid and shop_id=$shopID order by review_date desc limit 0,1";
    	
    	$result=$this->db->GetCol($sql);

    	if($result===false||$result[0]>=86400)
    		return false;
    	else
    		return true;
    		
    }

    public function GetReviewRankById( $groupid, $isred, $perpage, $cpage=1 ) {
        if( intval($perpage) == 0 ||
                intval($cpage) == 0) {
            return false;
        }

        $col = "redids";
        if( !$isred )
            $col = "blackids";
        
        $sql = "select $col from review_rank where id=$groupid";
        $reviewids = $this->db->GetValue( $sql );
        if( !empty( $reviewids )){
            $sql = "SELECT	r.*,
                                                       si.shop_name,
                                                        ubi.nick as nick,
                                                        ubi.logo_path as logo_path,
                                                        us.contribution as contribution
                                         FROM 	review r,
                                                        user_basic_info ubi,
                                                        user_statistic us,
                                                        shop_info si
                                        WHERE	r.user_id = ubi.id
                                          AND   r.shop_id = si.id
                                          AND	r.user_id = us.user_id
                                          AND   r.id in (".$reviewids.")
                                          ORDER BY FIELD(r.id, ".$reviewids.")";

            $rows = $this->getRows( $sql,$perpage,$cpage );
            return $this->GetReviewDetail( $rows );
        }
        else{
            return array();
        }
    }

    public function GetReviewRankCountById( $groupid, $isred ){
        $col = "redids";
        if( !$isred )
            $col = "blackids";

        $sql = "select $col from review_rank where id=$groupid";
        $reviewids = $this->db->GetValue( $sql );
        if( !empty( $reviewids )){
            $sql = "SELECT count(*)
                                         FROM 	review r,
                                                        user_basic_info ubi,
                                                        user_statistic us,
                                                        shop_info si
                                        WHERE	r.user_id = ubi.id
                                          AND   r.shop_id = si.id
                                          AND	r.user_id = us.user_id
                                          AND   r.id in (".$reviewids.")
                                          ORDER BY FIELD(r.id, ".$reviewids.")";

            return $this->db->GetValue( $sql );
        }
        else{
            return 0;
        }
    }

    public function GetReviewRankList( $limit ){
        $limit = intval($limit);
        $sql = "select id, title from review_rank order by id desc limit 0,".$limit;

        return $this->db->GetPlan( $sql );
    }
    
    public function GetReviewRankInfo ( $groupid ){
    
        $sql = "select id, title from review_rank where id=$groupid";

        return $this->db->GetRow( $sql );
    }
    
    /**
     * 淘宝review exist or not
     * @param type $taobao_reivew_id
     * @return type 
     */
    public function CheckReviewExist( $taobao_reivew_id ){
        $sql = "select id from review where taobao_review_id='$taobao_reivew_id'";
        return $this->db->GetRow( $sql );
    }
}
?>
